/**
 * 不需要登录即可使用的路由
 */
import { Router } from "express";
import svgCaptcha from "svg-captcha";
import dayjs from "dayjs";

import db from "../database.js";
import {
  checkPassword,
  makePassword,
  orderBy,
  splitNumUnits,
} from "../utils.js";
import { validate } from "../middlewares.js";
import validators from "../validate.js";
import multiAvatar from "@multiavatar/multiavatar";
import { signJWT } from "../jwt.js";

// 实例化路由对象
const router = Router();

const [expires, units] = splitNumUnits(process.env.expiresIn ?? "24h");

// 生成随机头像
router.get("/avatar/:id.svg", (req, res) => {
  res.type("image/svg+xml");
  res.send(multiAvatar(req.params.id));
});

// 用户激活
router.post(
  "/user/active/:id",
  validate(validators.password, validators.nickname),
  async (req, res) => {
    const { id } = req.params,
      { password, nickname } = req.body;
    // 获取用户信息
    const user = await db.findOne`SELECT * FROM User WHERE id = ${id}`;
    if (!user || user.active) {
      return res.send({
        success: false,
        message: "用户激活错误!",
      });
    }
    // 更新用户信息
    await db.run`UPDATE User
		SET password = ${makePassword(password)},
      nickname = ${nickname}, active = true WHERE id = ${id}`;
    res.send({
      success: true,
      message: "激活成功!",
    });
  }
);

// 用户登录
router.post(
  "/login",
  validate(validators.email, validators.password, validators.code),
  async (req, res) => {
    const { email, password, code } = req.body;
    // 验证验证码
    if (
      code != "unk!" &&
      code.toLowerCase() !== req.session.code?.toLowerCase()
    ) {
      return res.send({
        success: false,
        message: "验证码错误!",
      });
    }
    const user = await db.findOne`SELECT * FROM User WHERE email = ${email}`;
    if (user && checkPassword(password, user.password)) {
      if (!user.active) {
        return res.send({
          success: false,
          message: "账户已被禁止登录!",
        });
      }
      // 将用户 id 写入 session
      req.session.userId = user.id;
      //
      return res.send({
        success: true,
        message: "登录成功!",
        object: user,
        accessToken: await signJWT({ userId: user.id }),
        expires: dayjs().add(expires, units).format("YYYY/MM/DD HH:mm:ss"),
      });
    }
    res.send({
      success: false,
      message: "账号或密码错误!",
    });
  }
);

// 查询所有发布的博客
router.get(
  "/posts",
  validate(
    validators.query.title,
    validators.query.tags,
    validators.query.orderby(["hits", "createdAt"]),
    validators.query.limit,
    validators.query.offset
  ),
  async (req, res) => {
    const {
      title,
      tags,
      orderby = "-createdAt",
      authorId,
      limit = 5,
      offset = 0,
    } = req.query;
    // where
    const where = ["published = true"],
      params = [];
    if (title) {
      // 忽略大小写
      where.push("LOWER(title) like LOWER(?)");
      params.push(`%${title}%`);
    }
    if (authorId) {
      where.push("authorId = ?");
      params.push(authorId);
    }
    //
    let table = "Post as a join User as b on a.authorId = b.id";
    // 多表连接
    if (Array.isArray(tags) && tags.length > 0) {
      table =
        "Post as a JOIN User as b on a.authorId = b.id join PostTags as c on a.id = c.postId";
      where.push("c.tagId in (?)");
      params.push(tags);
    }
    // 计算总数
    const { count } = await db.findOne(
      `select count(DISTINCT a.id) as count from ${table}
      where ${where.join(" and ")}`,
      params
    );
    // 排序
    const _orderby = orderBy(orderby);
    // 分页查询
    const sql = `select a.*, b.nickname, b.avatar from ${table}
      where ${where.join(" and ")} ${_orderby ? `order by ${_orderby}` : ""}
      limit ${limit} offset ${offset};`;
    const posts = await db.findAll(sql, params);
    res.send({
      success: true,
      query: {
        count,
        limit,
        offset,
        page: offset / limit + 1,
        results: posts,
      },
    });
  }
);

// 查询所有标签中阅读量最多的文章
router.get("/posts/tags-top1", async (req, res) => {
  const posts =
    await db.findAll(`SELECT a.*, e.nickname, e.avatar, d.name as tagName, d.id as tagId
FROM Post AS a
  JOIN PostTags AS b ON a.id = b.postId
  JOIN (
      SELECT
          b.tagId,
          MAX(a.hits) AS max_hits
      FROM Post AS a
          JOIN PostTags AS b ON a.id = b.postId
          JOIN Tag AS c ON b.tagId = c.id and ownerId is NULL
      GROUP BY
          b.tagId
  ) AS c ON b.tagId = c.tagId
  JOIN Tag AS d ON c.tagId = d.id
  JOIN User AS e on a.authorId = e.id
  WHERE published = true and a.hits = c.max_hits`);
  res.send({
    success: true,
    query: {
      results: posts,
    },
  });
});

// 查询博客详情
router.get("/posts/:id", validate(validators.params.id), async (req, res) => {
  const { id } = req.params;
  // 判断该帖子是否存在
  const post = await db.findOne`SELECT a.*, b.nickname, b.avatar
FROM Post as a inner join User as b on a.authorId = b.id
WHERE published = true and a.id = ${id}`;
  if (!post) {
    return res.send({
      success: false,
      message: "该帖子不存在!",
    });
  }
  post.tags = await db.findAll`SELECT b.id, b.name
FROM PostTags a JOIN Tag b ON a.tagId = b.id
WHERE a.postId = ${id}`;
  // 增加阅读量
  await db.run`UPDATE Post SET hits = hits + 1 WHERE id = ${id}`;
  res.send({
    success: true,
    object: post,
  });
});

// 获取前10位活跃的作者
router.get("/users-top10", async (req, res) => {
  const users = await db.findAll`SELECT a.*, c.name, c.job FROM User AS a JOIN(
SELECT DISTINCT a.id, MAX(b.updatedAt) AS updatedAt
FROM User AS a JOIN Post AS b on b.authorId = a.id
WHERE published = true
GROUP BY a.id
ORDER BY updatedAt DESC) AS b ON a.id = b.id
JOIN Resume AS c ON a.id = c.authorId
ORDER BY b.updatedAt DESC
LIMIT 10`;
  res.send({
    success: true,
    query: {
      results: users,
    },
  });
});

// 统计所有标签的文章数量
router.get("/tags-count", async (req, res) => {
  const tags =
    await db.findAll`SELECT a.id, a.name, COUNT(DISTINCT c.id) AS count
FROM Tag AS a
  JOIN PostTags AS b ON a.id = b.tagId
  JOIN Post AS c ON b.postId = c.id
  WHERE c.published = true
  GROUP BY a.id, a.name
  ORDER BY count DESC`;
  res.send({
    success: true,
    query: {
      results: tags,
    },
  });
});

// 获取单个标签
router.get("/tags/:id", validate(validators.params.id), async (req, res) => {
  const { id } = req.params;
  const tag = await db.findOne`select * from Tag where id = ${id}`;
  if (!tag) {
    return res.send({
      success: false,
      message: "该标签不存在!",
    });
  }
  res.send({
    success: true,
    object: tag,
  });
});

// 获取作者列表：按发帖量排序
router.get(
  "/authors",
  validate(validators.query.limit, validators.query.offset),
  async (req, res) => {
    const { limit = 5, offset = 0 } = req.query;
    const { count } =
      await db.findOne`SELECT COUNT(a.id) AS count FROM User AS a
  JOIN Resume AS b on a.id = b.authorId`;
    const users = await db.findAll`SELECT a.*, b.name, b.job
FROM User AS a
  JOIN Resume AS b on a.id = b.authorId
  JOIN (
      SELECT
          authorId,
          COUNT(*) AS count
      FROM Post
      GROUP BY
          authorId
  ) AS c on c.authorId = a.id ORDER BY c.count DESC limit ${limit} offset ${offset}`;
    res.send({
      success: true,
      query: {
        count,
        limit,
        offset,
        page: offset / limit + 1,
        results: users,
      },
    });
  }
);

// 获取作者列表：按创建时间排序
router.get(
  "/authors-by",
  validate(validators.query.limit, validators.query.offset),
  async (req, res) => {
    const { limit = 30, offset = 0 } = req.query;
    const { count } =
      await db.findOne`SELECT COUNT(a.id) AS count FROM User AS a
  JOIN Resume AS b on a.id = b.authorId`;
    const users = await db.findAll`SELECT a.*, b.name, b.job
FROM User AS a
  JOIN Resume AS b on a.id = b.authorId
ORDER BY a.createdAt DESC limit ${limit} offset ${offset}`;
    res.send({
      success: true,
      query: {
        count,
        limit,
        offset,
        page: offset / limit + 1,
        results: users,
      },
    });
  }
);

router.get(
  "/author-details/:id",
  validate(validators.params.id),
  async (req, res) => {
    const { id } = req.params;
    const user =
      await db.findOne`select * from User as a JOIN Resume as b on a.id = b.authorId where a.id = ${id}`;
    if (!user) {
      return res.send({
        success: false,
        message: "该用户不存在!",
      });
    }
    res.send({
      success: true,
      object: user,
    });
  }
);

//

// 获取某位作者发布的所有文章
router.get(
  "/author-posts/:id",
  validate(
    validators.params.id,
    validators.query.limit,
    validators.query.offset
  ),
  async (req, res) => {
    const { limit, offset } = req.query;
    const { id } = req.params;
    const { count } =
      await db.findOne`SELECT COUNT(id) AS count FROM Post WHERE published = true and authorId = ${id}`;
    const posts = await db.findAll`select a.*,b.nickname,b.avatar 
      from Post AS a JOIN User AS b ON a.authorId = b.id
      where published = true and authorId = ${id}
      order by createdAt desc limit ${limit} offset ${offset}`;
    res.send({
      success: true,
      query: {
        count,
        limit,
        offset,
        page: offset / limit + 1,
        results: posts,
      },
    });
  }
);

// 获取评论
router.get(
  "/posts/:id/comments/",
  validate(
    validators.params.id,
    validators.query.limit,
    validators.query.offset
  ),
  async (req, res) => {
    const { limit, offset } = req.query,
      { id } = req.params;
    // 判断该帖子是否存在
    const exists = await db.findOne`select * from Post where id = ${id}`;
    if (!exists) {
      return res.send({
        success: false,
        message: "该帖子不存在!",
      });
    }
    const { count } =
      await db.findOne`select count(id) as count from Comment where postId = ${id}`;
    const comments = await db.findAll`select a.*, b.nickname, b.avatar
      from Comment a
      join User b on a.authorId = b.id  
      where postId = ${id} order by createdAt desc limit ${limit} offset ${offset}`;

    res.send({
      success: true,
      query: {
        count,
        limit,
        offset,
        page: offset / limit + 1,
        results: comments,
      },
    });
  }
);

// 验证码
const captcha = async (req, res) => {
  const captcha = svgCaptcha.create({
    noise: 4,
    color: true,
  });
  req.session.code = captcha.text;
  // 设置响应类型
  res.type("svg");
  res.send(captcha.data);
};

export default (app) => {
  app.use("/api", router);
  app.get("/captcha", captcha);
};
